package DBLayer;

import java.awt.event.ActionListener;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import ModelLayer.Clothing;
import ModelLayer.Equipment;
import ModelLayer.GunReplicas;
import ModelLayer.Product;
import ModelLayer.Supplier;



public class DBProduct extends DBEvents
{
	private DBSupplier dbSupplier;

	public DBProduct(ActionListener parent)
	{
		super(parent);
		// TODO Auto-generated constructor stub
		dbSupplier = new DBSupplier(parent);
	}
	
	
	private String buildWhere(boolean and,
			Integer id, Integer supplierId, Integer category, String name,
			Integer size, Integer color,
			String type, String description,
			String fabric, Integer calibre,
			Double purchasePrice, Boolean forRent, Double price,
			String countryOfOrigin, Integer minStock)
	{
		StringBuilder where = new StringBuilder();
		String andOr = and?" and ":" or ";
		
		where.append(((id == null)?""             :" " +
				((where.length() == 0)?"":andOr) +" id = '"              + id+"'"));		
		where.append(((supplierId == null)?""     :" " +
				((where.length() == 0)?"":andOr) +" supplierID = '"      + supplierId+   "'"));
		where.append(((category == null)?""       :" " +
				((where.length() == 0)?"":andOr) +" catagory = '"        + category+"'"));
		where.append(((name == null)?""           :" " +
				((where.length() == 0)?"":andOr) +" name = '"            + name+"'"));
		where.append(((size == null)?""           :" " +
				((where.length() == 0)?"":andOr) +" size = '"            + size+"'"));
		where.append(((color == null)?""          :" " +
				((where.length() == 0)?"":andOr) +" color = '"           + color+"'"));
		where.append(((type == null)?""           :" " +
				((where.length() == 0)?"":andOr) +" type = '"            + type+"'"));
		where.append(((description == null)?""    :" " +
				((where.length() == 0)?"":andOr) +" description = '"     + description+"'"));
		where.append(((fabric == null)?""         :" " +
				((where.length() == 0)?"":andOr) +" fabric = '"          + fabric+"'"));
		where.append(((calibre == null)?""        :" " +
				((where.length() == 0)?"":andOr) +" calibre = '"         + calibre+"'"));
		where.append(((purchasePrice == null)?""  :" " +
				((where.length() == 0)?"":andOr) +" purchasePrice = '"   + purchasePrice+"'"));
		where.append(((forRent == null)?""        :" " +
				((where.length() == 0)?"":andOr) +" forRent = '"         + forRent+"'"));
		where.append(((price == null)?""          :" " +
				((where.length() == 0)?"":andOr) +" price = '"           + price+"'"));
		where.append(((countryOfOrigin == null)?"":" " +
				((where.length() == 0)?"":andOr) +" countryOfOrigin = '" + countryOfOrigin+"'"));
		where.append(((minStock == null)?""       :" " +
				((where.length() == 0)?"":andOr) +" minStock = '"        + minStock+"'"));

		if (where.length() > 0) where.insert(1," where ");
		return where.toString();
	}
	
/*	
	public String test(String name, String address, Integer zipCode, String phoneNo)
	{
		return buildWhere(true, name, address, zipCode, phoneNo);
	}
	*/
	

	
	public List<Product> getAll()
	{
		return search(false,null,null,null,null,null,null,null,null,null,
				null,null,null,null,null,null);
	} // getAll
	

	public List<Product> search(boolean and,
			Integer id, Integer supplierId, Integer category, String name,
			Integer size, Integer color,
			String type, String description,
			String fabric, Integer calibre,
			Double purchasePrice, Boolean forRent, Double price,
			String countryOfOrigin, Integer minStock)
	{
		List<Product> products = new ArrayList<Product>();
		try
		{
			Statement statement = DBConnection.getInstance().getConnection().createStatement(
					ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			statement.setQueryTimeout(5);
			ResultSet cursor = statement.executeQuery("use DM79_4; " +
					"select id,supplierId,catagory,name," +
							"  size,color," +
							"  type,description," +
							"  fabric,calibre," +
							"purchasePrice,forRent,price," +
							"countryOfOrigin,minStock " +
					"from Product " +
					buildWhere(and,id,supplierId,category,name,
							size,color,
							type,description,
							fabric,calibre,
							purchasePrice,forRent,price,
							countryOfOrigin,minStock));
			
			Product product = null;
			while (cursor.next())
			{
				switch(cursor.getInt("catagory"))
				{
					case 1: 
						Clothing clothing = new Clothing();
						clothing.setSize(cursor.getInt("size"));
						clothing.setColor(cursor.getInt("color"));
						product = clothing;
						break;
					case 2:
						Equipment equipment = new Equipment();
						equipment.setType(cursor.getString("type"));
						equipment.setDescription(cursor.getString("description"));
						product = equipment;
						break;
					case 3:
						GunReplicas gunReplicas = new GunReplicas();
						gunReplicas.setFabric(cursor.getString("fabric"));
						gunReplicas.setCalibre(cursor.getInt("calibre"));
						product = gunReplicas;
						break;
				} // switch
				product.setId(cursor.getInt("id"));
				product.setName(cursor.getString("name"));
				product.setPurchasePrice(cursor.getDouble("purchasePrice"));
				product.setForRent(cursor.getBoolean("forRent"));
				product.setPrice(cursor.getDouble("price"));
			  product.setCountryOfOrigin(cursor.getString("countryOfOrigin"));
			  product.setMinStock(cursor.getInt("minStock"));
			
				List<Supplier> suppliers = dbSupplier.search(true,
						cursor.getInt("supplierId"),
						null,null,null,null,null,null);
				if (!suppliers.isEmpty()) product.setSupplier(suppliers.get(0));
				else product.setSupplier(null);
				
				products.add(product);
			//id,supplierId,category,name, purchasePrice,forRent,price, countryOfOrigin, minStock
				
			} // while
			cursor.close();
			statement.close();
		} 
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			// e.printStackTrace();
			tellListeners(e.getMessage(), DBEvents.FATAL);
		}
		return products;
	} // search
	

	public void update(int id, Product product)
	{
		int categoryId = 0;
		if (product instanceof Clothing)    categoryId = 1;
		if (product instanceof Equipment)   categoryId = 2;
		if (product instanceof GunReplicas) categoryId = 3;
		
		if (!DBConnection.startTransaction())
		{
			tellListeners(DBConnection.getException().toString(),DBEvents.FATAL);
		}
		else try
		{
			PreparedStatement updateProduct = 
					DBConnection.getInstance().getConnection().prepareStatement( 
							"use DM79_4; " +
							"update Product " +
							"set supplierId = ?, catagory = ?, name = ?," +
							" size = ?, color = ?," +
							" type = ?, decription = ?" +
							" fabric = ?, calibre = ?" +
							" purchasePrice = ?, forRent = ?, price = ?," +
							" countryOfOrigin = ?, minStock = ?" +
							"  where id = "+id);
			
			updateProduct.setQueryTimeout(5);
			
			//updateProduct.setInt(1,product.getId());
			updateProduct.setInt(1,product.getSupplier().getId());
			updateProduct.setInt(2,categoryId);
			updateProduct.setString(3,product.getName());
			
			switch (categoryId)
			{
			case 1 :
				updateProduct.setInt(4,((Clothing)product).getSize());
				updateProduct.setInt(5,((Clothing)product).getColor());
				break;
			case 2 :
				updateProduct.setString(6,((Equipment)product).getType());
				updateProduct.setString(7,((Equipment)product).getDescription());
				break;
			case 3 :
				updateProduct.setString(8,((GunReplicas)product).getFabric());
				updateProduct.setInt(9,((GunReplicas)product).getCalibre());
				break;
			}

			updateProduct.setDouble(10,product.getPurchasePrice());
			updateProduct.setBoolean(11,product.isForRent());
			updateProduct.setDouble(12,product.getPrice());
			updateProduct.setString(13,product.getCountryOfOrigin());
			updateProduct.setInt(14,product.getMinStock());
			
			updateProduct.execute();
			updateProduct.close();
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			// e.printStackTrace();
			tellListeners(e.getMessage(), DBEvents.FATAL);
		}
		if (!DBConnection.commitTransaction())
		{
			tellListeners(DBConnection.getException().toString(),DBEvents.NO_COMMIT);
			if (!DBConnection.rollbackTransaction())
			{
				tellListeners(DBConnection.getException().toString(),DBEvents.FATAL);
			}
		} // if
		else tellListeners("Update done",DBEvents.OK);
	} // update
	
	
	public void insert(Product product)
	{
		int categoryId = 0;
		if (product instanceof Clothing)    categoryId = 1;
		if (product instanceof Equipment)   categoryId = 2;
		if (product instanceof GunReplicas) categoryId = 3;
		
		if (!DBConnection.startTransaction())
		{
			tellListeners(DBConnection.getException().toString(),DBEvents.FATAL);
		}
		else try
		{
			PreparedStatement insertProduct = 
					DBConnection.getInstance().getConnection().prepareStatement(
							"use DM79_4; " +
							"insert into Product(id,supplierId,catagory,name," +
							"  size,color," +
							"  type,description," +
							"  fabric,calibre," +
							"purchasePrice,forRent,price," +
							"countryOfOrigin,minStock)" +
							"values(?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?)");
			
			insertProduct.setQueryTimeout(5);
			
			insertProduct.setInt(1,product.getId());
			insertProduct.setInt(2,product.getSupplier().getId());
			insertProduct.setInt(3,categoryId);
			insertProduct.setString(4,product.getName());
			
			switch (categoryId)
			{
			case 1 :
				insertProduct.setInt(5,((Clothing)product).getSize());
				insertProduct.setInt(6,((Clothing)product).getColor());
				break;
			case 2 :
				insertProduct.setString(7,((Equipment)product).getType());
				insertProduct.setString(8,((Equipment)product).getDescription());
				break;
			case 3 :
				insertProduct.setString(9,((GunReplicas)product).getFabric());
				insertProduct.setInt(10,((GunReplicas)product).getCalibre());
				break;
			}
			
			insertProduct.setDouble(11,product.getPurchasePrice());
			insertProduct.setBoolean(12,product.isForRent());
			insertProduct.setDouble(13,product.getPrice());
			insertProduct.setString(14,product.getCountryOfOrigin());
			insertProduct.setInt(15,product.getMinStock());

			insertProduct.execute();
			insertProduct.close();
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			// e.printStackTrace();
			tellListeners(e.getMessage(), DBEvents.FATAL);
		}
		if (!DBConnection.commitTransaction())
		{
			tellListeners(DBConnection.getException().toString(),DBEvents.NO_COMMIT);
			if (!DBConnection.rollbackTransaction())
			{
				tellListeners(DBConnection.getException().toString(),DBEvents.FATAL);
			}
		} // if
		else tellListeners("Insert done",DBEvents.OK);
	} // insert
	
	public void delete(int index)
	{
		if (!DBConnection.startTransaction())
		{
			tellListeners(DBConnection.getException().toString(),DBEvents.FATAL);
		}
		else try
		{
			Statement statement = DBConnection.getInstance().getConnection().createStatement();
			statement.setQueryTimeout(5);
			String deleteQuery = "use DM79_4; delete from Product where id = "+index;
			statement.executeUpdate(deleteQuery);
			statement.close();
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			// e.printStackTrace();
		}		
		if (!DBConnection.commitTransaction())
		{
			tellListeners(DBConnection.getException().toString(),DBEvents.NO_COMMIT);
			if (!DBConnection.rollbackTransaction())
			{
				tellListeners(DBConnection.getException().toString(),DBEvents.FATAL);
			}
		} // if
		else tellListeners("Delete done",DBEvents.OK);
	} // delete


}
